# @Time : 2021/3/6 14:02
# @Author : Fioman 
# @Phone : 13149920693
"""
使用连接池以及上下文管理来创建mysql的连接,方面mysql_handler来调用
"""
from datetime import datetime
from timeit import default_timer

import pymysql
from dbutils.pooled_db import PooledDB


class MysqlConfig(object):
    def __init__(self, host, db, user, password, port=3306):
        self.host = host  # 数据库ip地址
        self.port = port  # 数据库端口
        self.db = db  # 数据库库名
        self.user = user  # 用户名
        self.password = password  # 密码

        self.charset = "utf8mb4"
        self.minCached = 10  # 连接池中空闲连接的初始数量
        self.maxCached = 20  # 连接池中空闲连接的最大数量
        self.maxShared = 20  # 共享连接的最大数量
        self.maxConnection = 100  # 创建连接池的最大数量

        self.blocking = True  # 超过最大连接数量时候的表现,为True等待连接数量下降,为False直接报错处理
        self.maxUsage = 100  # 单个连接的最大重复使用次数
        self.setSession = None  # 可选的sql命令列表,用于准备会话,比如["set statestyle to ...","set time zone"]
        self.reset = True  # 当连接返回到池中时,应该如何重置连接.[False或者None 回滚到以begin()开始的事务,True,为了安全起见总是发出回滚]


class MySqlPoolConn(object):
    __pool = None

    def __init__(self, config):
        if not self.__pool:
            self.__class__.__pool = PooledDB(creator=pymysql,
                                             # maxconnections=config.maxConnection,
                                             # mincached=config.minCached,
                                             # maxcached=config.maxCached,
                                             # maxshared=config.maxShared,
                                             # blocking=config.blocking,
                                             # maxusage=config.maxUsage,
                                             # setsession=config.setSession,
                                             charset=config.charset,
                                             host=config.host,
                                             port=config.port,
                                             database=config.db,
                                             user=config.user,
                                             password=config.password,
                                             )

    def get_conn(self):
        return self.__pool.connection()


# 下面是带上下文连接的mysql应用的封装,在程序的开始初始化一个连接池
host = "localhost"
port = 3306
db = "xinfengjw_01"
user = "root"
password = "123456"

dbConfig = MysqlConfig(host, db, user, password, port)
globalPoolConn = MySqlPoolConn(dbConfig)


class MysqlOperator(object):
    def __init__(self, commit=True, logTime=False, logLabel="总用时"):
        """

        :param commit: 是否在最后提交事务(设置为False的时候方便单元测试)
        :param logTime: 是否打印程序运行总时间
        :param logLabel: 自定义log的文字
        """
        self._logTime = logTime
        self._commit = commit
        self._logLabel = logLabel

    def __enter__(self):
        # 如果需要记录时间
        if self._logTime:
            self._start = default_timer()

        # 从连接池获取数据连接
        conn = globalPoolConn.get_conn()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        conn.autocommit = False

        self._conn = conn
        self._cursor = cursor

        return self

    def __exit__(self, *excInfo):
        # 提交事务
        if self._commit:
            self._conn.commit()

        # 在退出的时候自动关闭连接和cursor
        self._cursor.close()
        self._conn.close()

        if self._logTime:
            timeUsed = default_timer() - self._start
            print("-- {}: {:.3f}".format(self._logLabel, timeUsed))

    def create_database(self, dabaseName):
        self.cursor.execute("create database if not exists {} default character set utf8".format(dabaseName))

    def delete_database(self, databaseName):
        self.cursor.execute("drop database {}".format(databaseName))

    def create_table(self, sql):
        self.cursor.execute(sql)

    def delete_table_data(self, tableName):
        self.cursor.execute("delete  from {}".format(tableName))

    def delete_table(self, tableName):
        self.cursor.execute("drop table {}".format(tableName))

    def insert_data(self, sql, params=None):
        self.cursor.execute(sql, params)

    def get_count(self, tableName):
        self.cursor.execute("select count(id) as total from {}".format(tableName))
        count = self.cursor.fetchone()["total"]
        return count

    def fetch_one(self, sql, params=None):
        """
        获取一条数据,其中params是什么意思呢?查询条件.
        :param sql:
        :param params:
        :return:
        """
        self.cursor.execute(sql, params)
        return self.cursor.fetchone()

    def fetch_all(self, tableName, params=None):
        """
        获取所有的数据
        :param sql:
        :param params:
        :return: 返回的是列表,里面包括了所有的记录,并且每条记录都是一个单独的字典集合.
        """
        sql = "Select * from {}".format(tableName)
        self.cursor.execute(sql, params)
        res = self.cursor.fetchall()
        dataNew = []
        for row in res:
            if "CREATETIME" in row and isinstance(row["CREATETIME"], datetime):
                row["CREATETIME"] = row["CREATETIME"].strftime("%Y-%m-%d %H:%M:%S")
            if "EMPTIME" in row and isinstance(row["EMPTIME"], datetime):
                row["EMPTIME"] = row["EMPTIME"].strftime("%Y-%m-%d %H:%M:%S")
            dataNew.append(row)
        return dataNew

    def delete_one_by_id(self, tableName, id):
        """
        根据id和表名删除一条记录.
        :param tableName:
        :param id:
        :return:
        """
        sql = "delete from {} where id = {}".format(tableName, id)
        self.cursor.execute(sql)

    def copy_table_to_hd(self, tableFrom, tableTo):
        """
        将中间表的内容复制一份到本地,目的是中间表的内容读取以后要删除掉
        @param tableFrom: 中间表表名
        @param tableTo:   本地表表名
        """
        sql = "insert into {} select * from {}".format(tableTo, tableFrom)
        self.cursor.execute(sql)

    def delete_record_by_field_val(self, tableName, fieldName, fieldVal):
        """
        根据字段的值去删除对应的记录
        @param tableName:
        @param fieldName:
        @param fieldVal:
        @return:
        """
        sql = f"delete from {tableName} where {fieldName} = '{fieldVal}'"
        self.cursor.execute(sql)

    @property
    def cursor(self):
        return self._cursor


if __name__ == '__main__':
    with MysqlOperator() as sp:
        sp.delete_table("PC_DATA_MANUALMATERIAL")
        sp.delete_table("PC_DATA_CRAFT")
